﻿using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace Housing
{
    public class HouseSituation
    {
        private int _hou_id;
        private int _hou_applyid;
        private int _hou_noid;
        private string _hou_address;
        private float _hou_area;
        private string _hou_owner;
        private string _hou_ownproof;
        private string _hou_nature;
        private string _hou_remark;
        private string _hou_addcounted;
        private int _effectDate;
        public int EffectDate
        {
            get { return _effectDate; }
            set { _effectDate = value; }
        }
        private int _applyid;
        public int Applyid
        {
            get { return _applyid; }
            set { _applyid = value; }
        }
        public int hou_id
        {
            get { return _hou_id; }
        }
        public int hou_applyid
        {
            get { return _hou_applyid; }
            set { _hou_applyid = value; }
        }
        public int hou_noid
        {
            get { return _hou_noid; }
            set { _hou_noid = value; }
        }
        public string hou_address
        {
            get { return _hou_address; }
            set { _hou_address = value; }
        }
        public float hou_area
        {
            get { return _hou_area; }
            set { _hou_area = value; }
        }
        public string hou_owner
        {
            get { return _hou_owner; }
            set { _hou_owner = value; }
        }
        public string hou_ownproof
        {
            get { return _hou_ownproof; }
            set { _hou_ownproof = value; }
        }
        public string hou_nature
        {
            get { return _hou_nature; }
            set { _hou_nature = value; }
        }
        public string hou_remark
        {
            get { return _hou_remark; }
            set { _hou_remark = value; }
        }
        public string hou_addcounted
        {
            get { return _hou_addcounted; }
            set { _hou_addcounted = value; }
        }

        public HouseSituation()
        {
        }

        public HouseSituation(int houId)
        {
            GetHouseSituationByHouId(houId);
        }

        public void GetHouseSituationByHouId(int houId)
        {
            string query = "SELECT * FROM Housing WHERE hou_id=" + houId;

            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, query);

            if (dr.Read())
            {
                _hou_id = (int)dr[0];
                _hou_applyid = Utils.GetNullableInt(dr[1]);
                _hou_noid = Utils.GetNullableInt(dr[2]);
                _hou_address = Utils.GetString(dr[3]);
                _hou_area = Utils.GetNullableFloat(dr[4]);
                _hou_owner = Utils.GetString(dr[5]);
                _hou_ownproof = Utils.GetString(dr[6]);
                _hou_nature = Utils.GetString(dr[7]);
                _hou_remark = Utils.GetString(dr[8]);
                _hou_addcounted = Utils.GetString(dr[9]);
            }

            dr.Close();
        }

        public int AddNewHouseSituation(HouseSituation hou)
        {
            string query = "INSERT INTO Housing(hou_applyid, hou_noid, hou_address, hou_area, hou_owner, hou_ownproof, hou_nature, hou_remark, hou_addcounted,applyid,EffectDate)"
                + "VALUES(" + hou.hou_applyid + "," + hou.hou_noid + ",'" + hou.hou_address + "'," + hou.hou_area + ",'" + hou.hou_owner + "',"
                + "'" + hou.hou_ownproof + "','" + hou.hou_nature + "','" + hou.hou_remark + "','" + hou.hou_addcounted + "'," + hou.Applyid + "," + hou.EffectDate + ")";

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, query);
        }

        public int UpdateHouseSituationByHouId(HouseSituation hou)
        {
            string query = "UPDATE Housing SET hou_applyid=" + hou.hou_applyid + ",hou_noid=" + hou.hou_noid + ","
                + "hou_address='" + hou.hou_address + "',hou_area=" + hou.hou_area + ",hou_owner='" + hou.hou_owner + "',"
                + "hou_ownproof='" + hou.hou_ownproof + "',hou_nature='" + hou.hou_nature + "',hou_remark='" + hou.hou_remark + "',hou_addcounted='" + hou.hou_addcounted + "'"
                + "WHERE hou_id=" + hou.hou_id;

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, query);
        }

        public int DeleteHouseSituationByHouId(int houId)
        {
            string query = "DELETE FROM Housing WHERE hou_id=" + houId;
            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, query);
        }

        public int DeleteHouseSituationByApplyid(string applyid)
        {
            string query = "DELETE FROM Housing WHERE hou_applyid=" + applyid;
            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, query);
        }

        public SqlDataReader GetHouseSituationByapplyid(string applyid)
        {
            string query = "SELECT * FROM Housing WHERE hou_applyid=" + applyid;
            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, query);
            return dr;
        }
    }
}